package com.mtl.inmigrant.shoes.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * This class is responsible for creating the database
 * @author MaryG
 *
 */


public class MyConnectionSQLiteHelper extends SQLiteOpenHelper{
	
	
	private static final String DATABASE_NAME = "mtlInmigrantShoes.db";
	private static final int DATABASE_VERSION = 7;
	
	public static final String TABLE1_ACTIVITY_INMIG = "Activity_I";
	public static final String COLUMN_TABLE1_ID_ACTIVITY = "id_A";
	public static final String COLUMN_TABLE1_STATUS = "status_A";
	public static final String COLUMN_TABLE1_DESCRIPTION = "descrip_A";
	
	
	public static final String TABLE2_TASK_INMIG = "Task_I";
	public static final String COLUMN_TABLE2_ID_ACTIVITY = "activity_id";
	public static final String COLUMN_TABLE2_ID_TASK = "id_T";
	public static final String COLUMN_TABLE2_DATE = "dateTask";
	public static final String COLUMN_TABLE2_STATUS = "status_T";
	public static final String COLUMN_TABLE2_DESCRIPTION = "descrip_T";
	
	
	public static final String TABLE3_REFERENTIAL = "Referential_I";
	public static final String COLUMN_TABLE3_ID_ACTIVITY = "activity_Id";
	public static final String COLUMN_TABLE3_ID_TASK = "task_id";
	public static final String COLUMN_TABLE3_ID_REFERENTIAL = "id_R";	
	public static final String COLUMN_TABLE3_STATUS = "status_R";
	public static final String COLUMN_TABLE3_DESCRIPTION = "descrip_R";
	
	
	
	// Database creation sql statement
	private static final String DATABASE_INMG_TABLE1_CREATE = "create table " 	
							+ TABLE1_ACTIVITY_INMIG + "( " + COLUMN_TABLE1_ID_ACTIVITY + " text primary key, " 
														   + COLUMN_TABLE1_STATUS + " integer, " 
														   + COLUMN_TABLE1_DESCRIPTION  + " text );";
	
	
	private static final String DATABASE_INMG_TABLE2_CREATE = "create table " 	
			+ TABLE2_TASK_INMIG + "( " + COLUMN_TABLE2_ID_ACTIVITY + " text, " 
										   + COLUMN_TABLE2_ID_TASK + " text," 
										   + COLUMN_TABLE2_DATE + " text,"
										   + COLUMN_TABLE2_STATUS + " integer, "
										   + COLUMN_TABLE2_DESCRIPTION + " text, " +
										   " PRIMARY KEY ( " + COLUMN_TABLE2_ID_TASK + "," + COLUMN_TABLE2_ID_ACTIVITY + ")," +
										   " FOREIGN KEY ( " + COLUMN_TABLE2_ID_ACTIVITY +
										   					" ) REFERENCES " 
										   					+ TABLE1_ACTIVITY_INMIG 
										   					+ " ( " + COLUMN_TABLE1_ID_ACTIVITY + " ) );";
		
	
	
	private static final String DATABASE_INMG_TABLE3_CREATE = "create table " 	
			+ TABLE3_REFERENTIAL + "( " + COLUMN_TABLE3_ID_ACTIVITY + " text, " 
										   + COLUMN_TABLE3_ID_TASK + " text,"
										   + COLUMN_TABLE3_ID_REFERENTIAL + " text," 
										   + COLUMN_TABLE3_STATUS + " integer, " 
										   + COLUMN_TABLE3_DESCRIPTION + " text, " +
										   "PRIMARY KEY ( " + COLUMN_TABLE3_ID_ACTIVITY + "," 
										   				    + COLUMN_TABLE3_ID_TASK + "," 
										   				    + COLUMN_TABLE3_ID_REFERENTIAL + "), " +
										   "FOREIGN KEY ( " + COLUMN_TABLE3_ID_ACTIVITY + 
										   				   ") REFERENCES "
										   				   + TABLE1_ACTIVITY_INMIG
										   				   + " ( " + COLUMN_TABLE1_ID_ACTIVITY +  ")," +
										   " FOREIGN KEY ( " + COLUMN_TABLE3_ID_TASK + 
										   				   " ) REFERENCES "
										   				   + TABLE2_TASK_INMIG
										   				   + " ( " + COLUMN_TABLE2_ID_TASK + " ) );";
	
	
	public MyConnectionSQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	
	@Override
	public void onCreate(SQLiteDatabase database) {
		//database.execSQL(DATABASE_INMG_TABLE1_CREATE + DATABASE_INMG_TABLE2_CREATE + DATABASE_INMG_TABLE3_CREATE);
		database.execSQL(DATABASE_INMG_TABLE1_CREATE);
		database.execSQL(DATABASE_INMG_TABLE2_CREATE);
		database.execSQL(DATABASE_INMG_TABLE3_CREATE);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(MyConnectionSQLiteHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE1_ACTIVITY_INMIG);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE2_TASK_INMIG);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE3_REFERENTIAL);
		//db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy)
		onCreate(db);
	}

}
